/**
 * CopyRight © 2012 USTC SINOVATE SOFTWARE CO.LTD All Rights Reserved.
 */

package com.cmcc.zysoft.infocollection.dao;

import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.text.SimpleDateFormat;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;
import com.cmcc.zysoft.infocollection.constant.Constant;
import com.cmcc.zysoft.infocollection.model.Resource;
import com.starit.common.dao.hibernate.HibernateBaseDaoImpl;

/**
 * @author 袁凤建
 * <br />邮箱: yuan.fengjian@ustcinfo.com
 * <br />描述: ResourcePcDao.java
 * <br />版本: 1.0.0
 * <br />日期: 2013-8-26 上午9:32:55
 * <br />CopyRight © 2012 USTC SINOVATE SOFTWARE CO.LTD All Rights Reserved.
 */

@Repository
public class ResourcePcDao extends HibernateBaseDaoImpl<Resource, String> {

	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	/**
	 * 获取所有入库状态为入库等待(state=1)或入库中(state=2)的资源信息.
	 * @return List<Resource>
	 */
	@SuppressWarnings("unchecked")
	public List<Resource> getResourceList() {
		String hql = "From Resource a where a.delFlag = '0' AND (a.state = '1' or a.state = '2')";
		return this.getHibernateTemplate().find(hql);
	}
	
	/**
	 * 根据流水号查询商户信息.
	 * @param infoId 流水号
	 * @return List<Resource>
	 */
	public List<Resource> getResourceByInfoId(String infoId) {
		return this.findByNamedParam(new String[]{"infoId", "delFlag"}, new Object[]{infoId, "0"});
	}
	
	/**
	 * 获取资源分页列表信息.
	 * @param provinceId 省份ID
	 * @param cityId 城市ID
	 * @param channelId 频道ID
	 * @param checkFlag 是否经过审核(0-是,1-否)
	 * @param dataFlag 数据标识(0-新增,1-更新)
	 * @param shopName 商户名称
	 * @param startTime 开始时间
	 * @param endTime 结束时间
	 * @param page 页码
	 * @param rows 每页记录数
	 * @return Map<String, Object>
	 */
	public Map<String, Object> resourcePage(String provinceId, String cityId, String channelId, String checkFlag, String dataFlag, 
			String shopName, String startTime, String endTime, int page, int rows) {
		int offset = (page - 1) * rows;
		Map<String, Object> map = new HashMap<String, Object>();
		String rowSql = 
				" SELECT a.resource_id AS resourceId, a.shop_id AS shopId, a.name AS 'name', a.telno AS telno, a.address AS " +
				" address, a.branch_name AS branchName, a.short_name AS shortName, a.introduction AS introduction, a.recommendation AS " +
				" recommendation, a.feature AS feature, a.special_offer AS specialOffer, a.province_id AS provinceId, a.city_id AS " +
				" cityId, a.district_id AS districtId, a.channel_id AS channelId, a.trade_id AS tradeId, a.belong_id AS belongId, a.x " +
				" AS 'x', a.y AS 'y', a.landmark AS landmark, a.region AS region, a.xy_level AS xyLevel, a.tag AS tag, a.collector_id " +
				" AS collectorId, a.is_outbound AS isOutbound, a.zipcode AS zipcode, a.parking AS parking, a.charge_type AS chargeType, " +
				" a.open_time AS openTime, a.bus_route AS busRoute, a.route AS route, a.cost AS cost, a.area AS 'area', a.private_room " +
				" AS privateRoom, a.ticket_price AS ticketPrice, a.max_capacity AS maxCapacity, a.min_capacity AS minCapacity, " +
				" a.capacity AS capacity, a.reservation AS reservation, a.delivery_service AS deliveryService, a.official_site AS " +
				" officialSite, a.mobile_payment AS mobilePayment, a.info_maintain AS infoMaintain, a.data_source AS dataSource, " +
				" a.check_flag AS checkFlag, a.data_flag AS dataFlag, a.add_man AS addMan, a.add_date AS 'addDate', a.del_flag AS " +
				" delFlag, a.update_type AS updateType, a.info_id AS infoId, a.state AS state, (SELECT item.service_item_name FROM " +
				" tb_b_service_item item WHERE item.service_item_id = a.belong_id) AS serviceName, (SELECT industry.industry_name FROM " +
				" tb_b_service_item item, tb_b_industry industry WHERE item.industry_id = industry.industry_id AND item.service_item_id " +
				" = a.belong_id) AS industryName, (SELECT b.real_name FROM tb_c_system_user b WHERE a.add_man = b.user_id) AS userName " +
				" FROM tb_b_resource a " +
				" WHERE a.del_flag = '0' ";
		String exportSql = 
				" SELECT a.resource_id AS resourceId, a.shop_id AS shopId, a.name AS 'name', a.telno AS telno, a.address AS " +
				" address, a.branch_name AS branchName, a.short_name AS shortName, a.introduction AS introduction, a.recommendation AS " +
				" recommendation, a.feature AS feature, a.special_offer AS specialOffer, a.x AS 'x', a.y AS 'y', a.landmark AS landmark, " +
				" a.region AS region, a.xy_level AS xyLevel, a.tag AS tag, a.collector_id AS collectorId, '否' AS isOutbound, a.zipcode " +
				" AS zipcode, a.parking AS parking, a.charge_type AS chargeType, a.open_time AS openTime, a.bus_route AS busRoute, " +
				" a.route AS route, a.cost AS cost, a.area AS 'area', a.private_room AS privateRoom, a.ticket_price AS ticketPrice, " +
				" a.max_capacity AS maxCapacity, a.min_capacity AS minCapacity, a.capacity AS capacity, a.reservation AS reservation, " +
				" a.delivery_service AS deliveryService, a.official_site AS officialSite, a.mobile_payment AS mobilePayment, a.info_maintain " +
				" AS infoMaintain, a.data_source AS dataSource, a.check_flag AS checkFlag, a.add_man AS addMan, a.add_date AS 'addDate', " +
				" a.del_flag AS delFlag, a.update_type AS updateType, a.info_id AS infoId, a.state AS state, " +
				" (CASE a.data_flag WHEN '0'THEN '新增' WHEN '1' THEN '更新' ELSE '删除' END) AS dataFlag, " +
				" (SELECT channel_name FROM tb_b_channel WHERE channel_id = a.channel_id) AS channelName, " +
				" (SELECT industry_name FROM tb_b_industry WHERE industry_id = a.trade_id) AS industryName, " +
				" (SELECT service_item_name FROM tb_b_service_item WHERE  service_item_id = a.belong_id) AS serviceName, " +
				" (SELECT province FROM provinces WHERE a.province_id = provinceid) AS province, " +
				" (SELECT city FROM cities WHERE a.city_id = cityid) AS city, " +
				" (SELECT AREA FROM areas WHERE a.district_id = areaid) AS district, " +
				" (SELECT user_name FROM tb_c_system_user WHERE user_id = a.add_man) AS mobile " +
				" FROM tb_b_resource a " +
				" WHERE a.del_flag = '0' ";
		String countSql = "SELECT COUNT(*) FROM tb_b_resource a WHERE a.del_flag = '0'";
		if(StringUtils.hasText(provinceId)) {
			rowSql += " AND a.province_id = '" + provinceId + "'";
			countSql += " AND a.province_id = '" + provinceId + "'";
			exportSql += " AND a.province_id = '" + provinceId + "'";
		}
		if(StringUtils.hasText(cityId)) {
			rowSql += " AND a.city_id = '" + cityId + "'";
			countSql += " AND a.city_id = '" + cityId + "'";
			exportSql += " AND a.city_id = '" + cityId + "'";
		}
		if(StringUtils.hasText(channelId)) {
			rowSql += " AND a.channel_id = '" + channelId + "'";
			countSql += " AND a.channel_id = '" + channelId + "'";
			exportSql += " AND a.channel_id = '" + channelId + "'";
		}
		if(StringUtils.hasText(checkFlag)) {
			rowSql += " AND a.check_flag = '" + checkFlag + "'";
			countSql += " AND a.check_flag = '" + checkFlag + "'";
			exportSql += " AND a.check_flag = '" + checkFlag + "'";
		}
		if(StringUtils.hasText(dataFlag)) {
			rowSql += " AND a.data_flag = '" + dataFlag + "'";
			countSql += " AND a.data_flag = '" + dataFlag + "'";
			exportSql += " AND a.data_flag = '" + dataFlag + "'";
		}
		if(StringUtils.hasText(shopName)) {
			rowSql += " AND a.name LIKE '%" + shopName + "%'";
			countSql += " AND a.name LIKE '%" + shopName + "%'";
			exportSql += " AND a.name LIKE '%" + shopName + "%'";
		}
		if(StringUtils.hasText(startTime)) {
			rowSql += " AND a.add_date >= '" + startTime + " 00:00:00'";
			countSql += " AND a.add_date >= '" + startTime + " 00:00:00'";
			exportSql += " AND a.add_date >= '" + startTime + " 00:00:00'";
		}
		if(StringUtils.hasText(endTime)) {
			rowSql += " AND a.add_date <= '" + endTime + " 23:59:59'";
			countSql += " AND a.add_date <= '" + endTime + " 23:59:59'";
			exportSql += " AND a.add_date <= '" + endTime + " 23:59:59'";
		}
		exportSql += " ORDER BY a.add_date DESC ";
		rowSql += " ORDER BY a.add_date DESC LIMIT ?, ? ";
		((ServletRequestAttributes)RequestContextHolder.getRequestAttributes()).getRequest().getSession().setAttribute(Constant.EXPORT_EXCEL_SQL, exportSql);
		List<Map<String, Object>> list = this.jdbcTemplate.queryForList(rowSql, offset, rows);
		int total = this.jdbcTemplate.queryForInt(countSql);
		map.put("rows", list);
		map.put("total", total);
		return map;
	}
	
	/**
	 * 用户使用情况统计.
	 * @param companyId 企业ID
	 * @param departmentId 部门ID
	 * @param keyword 搜索关键字(用户姓名/手机号)
	 * @param startTime 开始时间
	 * @param endTime 结束时间
	 * @param page 页码
	 * @param rows 每页记录数
	 * @return Map<String, Object>
	 */
	public Map<String, Object> statisticInfo(String companyId, String departmentId, String keyword, String startTime, String endTime, int page, int rows) {
		String nowDate = new SimpleDateFormat("yyyy-MM-dd").format(new Date());
		int offset = (page - 1) * rows;
		Map<String, Object> map = new HashMap<String, Object>();
		String rowSql = 
				" SELECT c.company_name AS companyName, d.department_name AS departmentName, e.employee_name AS employeeName, e.mobile, " +
				" (SELECT COUNT(*) FROM tb_b_resource r1 WHERE r1.add_man = r.add_man AND r1.data_flag = '0' AND r1.del_flag = '0' AND r1.add_date LIKE '%" + nowDate + "%') AS todayCollection, " +
				" (SELECT COUNT(*) FROM tb_b_resource r1 WHERE r1.add_man = r.add_man AND r1.data_flag = '1' AND r1.del_flag = '0' AND r1.add_date LIKE '%" + nowDate + "%') AS todayUpdate, ";
		if(StringUtils.hasText(startTime) && StringUtils.hasText(endTime)) {
			rowSql += " (SELECT COUNT(*) FROM tb_b_resource r1 WHERE r1.add_man = r.add_man AND r1.data_flag = '0' AND r1.del_flag = '0' AND r1.add_date >= '" + startTime + " 00:00:00' AND r1.add_date <= '" + endTime + " 23:59:59') AS totalCollection, " +
					" (SELECT COUNT(*) FROM tb_b_resource r1 WHERE r1.add_man = r.add_man AND r1.data_flag = '1' AND r1.del_flag = '0' AND r1.add_date >= '" + startTime + " 00:00:00' AND r1.add_date <= '" + endTime + " 23:59:59') AS totalUpdate ";
		} else if(StringUtils.hasText(startTime)) {
			rowSql += " (SELECT COUNT(*) FROM tb_b_resource r1 WHERE r1.add_man = r.add_man AND r1.data_flag = '0' AND r1.del_flag = '0' AND r1.add_date >= '" + startTime + " 00:00:00') AS totalCollection, " +
					" (SELECT COUNT(*) FROM tb_b_resource r1 WHERE r1.add_man = r.add_man AND r1.data_flag = '1' AND r1.del_flag = '0' AND r1.add_date >= '" + startTime + " 00:00:00') AS totalUpdate ";
		} else if(StringUtils.hasText(endTime)) {
			rowSql += " (SELECT COUNT(*) FROM tb_b_resource r1 WHERE r1.add_man = r.add_man AND r1.data_flag = '0' AND r1.del_flag = '0' AND r1.add_date <= '" + endTime + " 23:59:59') AS totalCollection, " +
					" (SELECT COUNT(*) FROM tb_b_resource r1 WHERE r1.add_man = r.add_man AND r1.data_flag = '1' AND r1.del_flag = '0' AND r1.add_date <= '" + endTime + " 23:59:59') AS totalUpdate ";
		} else {
			rowSql += " (SELECT COUNT(*) FROM tb_b_resource r1 WHERE r1.add_man = r.add_man AND r1.data_flag = '0' AND r1.del_flag = '0') AS totalCollection, " +
					" (SELECT COUNT(*) FROM tb_b_resource r1 WHERE r1.add_man = r.add_man AND r1.data_flag = '1' AND r1.del_flag = '0') AS totalUpdate ";
		}
		rowSql += " FROM tb_b_resource r, tb_c_system_user u, tb_c_employee e, tb_c_department d, tb_c_company c " +
				" WHERE r.add_man = u.user_id AND u.employee_id = e.employee_id AND e.department_id = d.department_id " +
				" AND c.company_id = u.company_id AND r.del_flag = '0'";
		if(StringUtils.hasText(keyword)) {
			rowSql += " AND (e.employee_name LIKE '%" + keyword + "%' OR e.mobile LIKE '%" + keyword + "%') ";
		}
		if(!"0".equals(companyId) && StringUtils.hasText(companyId)) {
			rowSql += " AND c.company_id = '" + companyId + "' ";
		}
		if(!"0".equals(departmentId) && StringUtils.hasText(departmentId)) {
			rowSql += " AND d.department_id = '" + departmentId + "' ";
		}
		rowSql += " GROUP BY r.add_man LIMIT ?, ? ";
		List<Map<String, Object>> list = this.jdbcTemplate.queryForList(rowSql, offset, rows);
		map.put("rows", list);
		map.put("total", list.size());
		return map;
	}
	
	/**
	 * 企业统计详细信息.
	 * @param companyId 企业ID
	 * @param startTime 开始时间
	 * @param endTime 结束时间
	 * @param page 页码
	 * @param rows 每页记录数
	 * @return Map<String, Object>
	 */
	public Map<String, Object> companyStatInfo(String companyId, String startTime, String endTime, int page, int rows) {
		StringBuffer sb = new StringBuffer();
		String nowDate = new SimpleDateFormat("yyyy-MM-dd").format(new Date());
		int offset = (page - 1) * rows;
		Map<String, Object> map = new HashMap<String, Object>();
		sb.append("   SELECT aa.company_id AS companyId, aa.company_name AS companyName, aa.company_code AS companyCode, ")
			.append("	 	 IFNULL(num1, 0) AS todayCollection, IFNULL(num2, 0) AS todayUpdate, ")
			.append("	 	 IFNULL(num3, 0) AS totalCollection, IFNULL(num4, 0) AS totalUpdate ")
			.append(" FROM ( ")
			.append("  	 	SELECT a.company_id, a.company_name, a.company_code ")
			.append("	 	FROM tb_c_company a ")
			.append(" 	 	WHERE a.del_flag = '0' ");
		if(!"0".equals(companyId) && StringUtils.hasText(companyId)) {
			sb.append("  	AND a.company_id = '" + companyId + "' ");
		}
		sb.append("   ) aa LEFT JOIN ( ")
			.append(" 		SELECT a.company_id, COUNT(*) AS num1 ")
			.append(" 		FROM tb_c_company a, tb_b_resource b, tb_c_system_user c ")
			.append(" 		WHERE a.company_id = c.company_id AND b.add_man = c.user_id AND b.del_flag = '0' AND b.data_flag = '0' AND b.add_date LIKE '%" + nowDate + "%' ")
			.append(" 		GROUP BY a.company_id ")
			.append(" ) bb ON aa.company_id = bb.company_id LEFT JOIN ( ")
			.append(" 		SELECT a.company_id, COUNT(*) AS num2 ")
			.append(" 		FROM tb_c_company a, tb_b_resource b, tb_c_system_user c ")
			.append(" 		WHERE a.company_id = c.company_id AND b.add_man = c.user_id AND b.del_flag = '0' AND b.data_flag = '1' AND b.add_date LIKE '%" + nowDate + "%' ")
			.append(" 		GROUP BY c.company_id ")
			.append(" ) cc ON aa.company_id = cc.company_id LEFT JOIN ( ")
			.append(" 		SELECT a.company_id, COUNT(*) AS num3 ")
			.append(" 		FROM tb_c_company a, tb_b_resource b, tb_c_system_user c ");
		if(StringUtils.hasText(startTime) && StringUtils.hasText(endTime)) {
			sb.append(" 	WHERE a.company_id = c.company_id AND b.add_man = c.user_id AND b.del_flag = '0' AND b.data_flag = '0' AND b.add_date >= '" + startTime + " 00:00:00' AND b.add_date <= '" + endTime + " 23:59:59' ");
		} else if(StringUtils.hasText(startTime)) {
			sb.append(" 	WHERE a.company_id = c.company_id AND b.add_man = c.user_id AND b.del_flag = '0' AND b.data_flag = '0' AND b.add_date >= '" + startTime + " 00:00:00' ");
		} else if(StringUtils.hasText(endTime)) {
			sb.append(" 	WHERE a.company_id = c.company_id AND b.add_man = c.user_id AND b.del_flag = '0' AND b.data_flag = '0' AND b.add_date <= '" + endTime + " 23:59:59' ");
		} else {
			sb.append(" 	WHERE a.company_id = c.company_id AND b.add_man = c.user_id AND b.del_flag = '0' AND b.data_flag = '0' ");
		}
		sb.append(" 		GROUP BY c.company_id ")
			.append(" ) dd ON aa.company_id = dd.company_id LEFT JOIN ( ")
			.append(" 		SELECT a.company_id, COUNT(*) AS num4 ")
			.append(" 		FROM tb_c_company a, tb_b_resource b, tb_c_system_user c ");
		if(StringUtils.hasText(startTime) && StringUtils.hasText(endTime)) {
			sb.append(" 	WHERE a.company_id = c.company_id AND b.add_man = c.user_id AND b.del_flag = '0' AND b.data_flag = '1' AND b.add_date >= '" + startTime + " 00:00:00' AND b.add_date <= '" + endTime + " 23:59:59' ");
		} else if(StringUtils.hasText(startTime)) {
			sb.append(" 	WHERE a.company_id = c.company_id AND b.add_man = c.user_id AND b.del_flag = '0' AND b.data_flag = '1' AND b.add_date >= '" + startTime + " 00:00:00' ");
		} else if(StringUtils.hasText(endTime)) {
			sb.append(" 	WHERE a.company_id = c.company_id AND b.add_man = c.user_id AND b.del_flag = '0' AND b.data_flag = '1' AND b.add_date <= '" + endTime + " 23:59:59' ");
		} else {
			sb.append(" 	WHERE a.company_id = c.company_id AND b.add_man = c.user_id AND b.del_flag = '0' AND b.data_flag = '1' ");
		}
		sb.append(" 		GROUP BY c.company_id ")
			.append(" ) ee ON aa.company_id = ee.company_id ");
		String rowSql = sb.toString();
		rowSql += " LIMIT ?, ? ";
		List<Map<String, Object>> list = this.jdbcTemplate.queryForList(rowSql, offset, rows);
		map.put("rows", list);
		map.put("total", list.size());
		return map;
	}
	
	/**
	 * 部门统计详细信息.
	 * @param parentDepartmentId 父部门ID
	 * @param companyId 企业ID
	 * @param startTime 开始时间
	 * @param endTime 结束时间
	 * @return List<Map<String, Object>>
	 */
	public List<Map<String, Object>> deptStatInfo(String parentDepartmentId, String companyId, String startTime, String endTime) {
		StringBuffer sb = new StringBuffer();
		String nowDate = new SimpleDateFormat("yyyy-MM-dd").format(new Date());
		sb.append("   SELECT dept.*, comp.company_name AS companyName, 'folder' AS iconCls, dept.department_id AS id ,dept.department_name AS text, ")
			.append("       (CASE dept.have_child_deparment WHEN 'N' THEN 'open' WHEN 'Y' THEN 'closed' ELSE 'open' END) AS state, ")
			.append("       (CASE dept.have_child_deparment WHEN 'N' THEN TRUE WHEN 'Y' THEN FALSE ELSE TRUE END) AS isLeaf, ")
			.append(" 		( ")
			.append("   		SELECT COUNT(*) FROM tb_b_resource a, tb_c_system_user b, tb_c_employee c ")
			.append("			WHERE a.add_man = b.user_id AND c.department_id = dept.department_id AND b.employee_id = c.employee_id ")
			.append(" 				 AND a.del_flag = '0' AND a.data_flag = '0' AND a.add_date LIKE '%" + nowDate + "%' ")
			.append(" 		) AS todayCollection, ( ")
			.append("			SELECT COUNT(*) FROM tb_b_resource a, tb_c_system_user b, tb_c_employee c ")
			.append("			WHERE a.add_man = b.user_id AND c.department_id = dept.department_id AND b.employee_id = c.employee_id ")
			.append("				  AND a.del_flag = '0' AND a.data_flag = '1' AND a.add_date LIKE '%" + nowDate + "%' ")
			.append("		) AS todayUpdate, ( ")
			.append("			SELECT COUNT(*) ")
			.append("			FROM tb_b_resource a, tb_c_system_user b, tb_c_employee c ")
			.append("			WHERE a.add_man = b.user_id AND c.department_id = dept.department_id AND b.employee_id = c.employee_id AND a.del_flag = '0' AND a.data_flag = '0' ");
		if(StringUtils.hasText(startTime) && StringUtils.hasText(endTime)) {
			sb.append(" 			  AND a.add_date >= '" + startTime + " 00:00:00' AND a.add_date <= '" + endTime + " 23:59:59' ");
		} else if(StringUtils.hasText(startTime)) {
			sb.append(" 			  AND a.add_date >= '" + startTime + " 00:00:00' ");
		} else if(StringUtils.hasText(endTime)) {
			sb.append(" 		   	  AND a.add_date <= '" + endTime + " 23:59:59' ");
		}
		sb.append(" 		) AS totalCollection, ( ")
			.append(" 			SELECT COUNT(*) ")
			.append("			FROM tb_b_resource a, tb_c_system_user b, tb_c_employee c ")
			.append("			WHERE a.add_man = b.user_id AND c.department_id = dept.department_id AND b.employee_id = c.employee_id AND a.del_flag = '0' AND a.data_flag = '1' ");
		if(StringUtils.hasText(startTime) && StringUtils.hasText(endTime)) {
			sb.append(" 			  AND a.add_date >= '" + startTime + " 00:00:00' AND a.add_date <= '" + endTime + " 23:59:59' ");
		} else if(StringUtils.hasText(startTime)) {
			sb.append(" 			  AND a.add_date >= '" + startTime + " 00:00:00' ");
		} else if(StringUtils.hasText(endTime)) {
			sb.append(" 		   	  AND a.add_date <= '" + endTime + " 23:59:59' ");
		}
		sb.append("			) AS totalUpdate ")
			.append("	FROM tb_c_department dept, tb_c_company comp ")
			.append("	WHERE dept.del_flag='0' AND dept.company_id = comp.company_id AND dept.company_id = ? AND dept.parent_department_id = ? ")
			.append(" 	ORDER BY dept.display_order ");
		String sql = sb.toString();
		return this.jdbcTemplate.queryForList(sql, companyId, parentDepartmentId);
	}
	
	/**
	 * 获取采集信息新增、修改统计次数.
	 * @return List<Map<String,Object>>
	 */
	public List<Map<String, Object>> getResourceCount() {
		String sql = 
				"select (select count(*) from tb_b_resource where month(add_date) = '01' and year(add_date) = year(current_date) and data_flag = '0' and del_flag = '0') as month1, " +
				"(select count(*) from tb_b_resource where month(add_date) = '02' and year(add_date) = year(current_date) and data_flag = '0' and del_flag = '0') as month2, " +
				"(select count(*) from tb_b_resource where month(add_date) = '03' and year(add_date) = year(current_date) and data_flag = '0' and del_flag = '0') as month3, " +
				"(select count(*) from tb_b_resource where month(add_date) = '04' and year(add_date) = year(current_date) and data_flag = '0' and del_flag = '0') as month4, " +
				"(select count(*) from tb_b_resource where month(add_date) = '05' and year(add_date) = year(current_date) and data_flag = '0' and del_flag = '0') as month5, " +
				"(select count(*) from tb_b_resource where month(add_date) = '06' and year(add_date) = year(current_date) and data_flag = '0' and del_flag = '0') as month6, " +
				"(select count(*) from tb_b_resource where month(add_date) = '07' and year(add_date) = year(current_date) and data_flag = '0' and del_flag = '0') as month7, " +
				"(select count(*) from tb_b_resource where month(add_date) = '08' and year(add_date) = year(current_date) and data_flag='0' and del_flag = '0') as month8, " +
				"(select count(*) from tb_b_resource where month(add_date) = '09' and year(add_date) = year(current_date) and data_flag = '0' and del_flag = '0') as month9, " +
				"(select count(*) from tb_b_resource where month(add_date) = '10' and year(add_date) = year(current_date) and data_flag = '0' and del_flag = '0') as month10, " +
				"(select count(*) from tb_b_resource where month(add_date) = '11' and year(add_date) = year(current_date) and data_flag = '0' and del_flag = '0') as month11, " +
				"(select count(*) from tb_b_resource where month(add_date) = '12' and year(add_date) = year(current_date) and data_flag = '0' and del_flag = '0') as month12 " +
				" union all " +
				" select (select count(*) from tb_b_resource where month(add_date) = '01' and year(add_date) = year(current_date) and data_flag = '1' and del_flag = '0') as month1," +
				"(select count(*) from tb_b_resource where month(add_date) = '02' and year(add_date) = year(current_date) and data_flag = '1' and del_flag = '0') as month2, " +
				"(select count(*) from tb_b_resource where month(add_date) = '03' and year(add_date) = year(current_date) and data_flag = '1' and del_flag = '0') as month3, " +
				"(select count(*) from tb_b_resource where month(add_date) = '04' and year(add_date) = year(current_date) and data_flag = '1' and del_flag = '0') as month4, " +
				"(select count(*) from tb_b_resource where month(add_date) = '05' and year(add_date) = year(current_date) and data_flag = '1' and del_flag = '0') as month5, " +
				"(select count(*) from tb_b_resource where month(add_date) = '06' and year(add_date) = year(current_date) and data_flag = '1' and del_flag = '0') as month6, " +
				"(select count(*) from tb_b_resource where month(add_date) = '07' and year(add_date) = year(current_date) and data_flag = '1' and del_flag = '0') as month7, " +
				"(select count(*) from tb_b_resource where month(add_date) = '08' and year(add_date) = year(current_date) and data_flag = '1' and del_flag = '0') as month8, " +
				"(select count(*) from tb_b_resource where month(add_date) = '09' and year(add_date) = year(current_date) and data_flag = '1' and del_flag = '0') as month9, " +
				"(select count(*) from tb_b_resource where month(add_date) = '10' and year(add_date) = year(current_date) and data_flag = '1' and del_flag = '0') as month10, " +
				"(select count(*) from tb_b_resource where month(add_date) = '11' and year(add_date) = year(current_date) and data_flag = '1' and del_flag = '0') as month11, " +
				"(select count(*) from tb_b_resource where month(add_date) = '12' and year(add_date) = year(current_date) and data_flag = '1' and del_flag = '0') as month12 ";
		return this.jdbcTemplate.queryForList(sql);
	}
	
	
	/**
	 * 获取采集信息新增、修改各地市总次数.
	 * @return List<Map<String,Object>>
	 */
	public List<Map<String, Object>> getResourceSumCount() {
		String sql = 
				"SELECT (SELECT COUNT(*) FROM tb_b_resource WHERE del_flag = '0' AND city_id = '21010000') AS '合肥市', " +
				"(SELECT COUNT(*) FROM tb_b_resource WHERE del_flag = '0' AND city_id = '21020000') AS '芜湖市', " +
				"(SELECT COUNT(*) FROM tb_b_resource WHERE del_flag = '0' AND city_id = '21030000') AS '蚌埠市', " +
				"(SELECT COUNT(*) FROM tb_b_resource WHERE del_flag = '0' AND city_id = '21040000') AS '淮南市', " +
				"(SELECT COUNT(*) FROM tb_b_resource WHERE del_flag = '0' AND city_id = '21050000') AS '马鞍山市', " +
				"(SELECT COUNT(*) FROM tb_b_resource WHERE del_flag = '0' AND city_id = '21060000') AS '淮北市', " +
				"(SELECT COUNT(*) FROM tb_b_resource WHERE del_flag = '0' AND city_id = '21070000') AS '铜陵市', " +
				"(SELECT COUNT(*) FROM tb_b_resource WHERE del_flag = '0' AND city_id = '21080000') AS '安庆市', " +
				"(SELECT COUNT(*) FROM tb_b_resource WHERE del_flag = '0' AND city_id = '21090000') AS '黄山市', " +
				"(SELECT COUNT(*) FROM tb_b_resource WHERE del_flag = '0' AND city_id = '21100000') AS '滁州市', " +
				"(SELECT COUNT(*) FROM tb_b_resource WHERE del_flag = '0' AND city_id = '21110000') AS '阜阳市', " +
				"(SELECT COUNT(*) FROM tb_b_resource WHERE del_flag = '0' AND city_id = '21120000') AS '宿州市', " +
				"(SELECT COUNT(*) FROM tb_b_resource WHERE del_flag = '0' AND city_id = '21140000') AS '六安市', " +
				"(SELECT COUNT(*) FROM tb_b_resource WHERE del_flag = '0' AND city_id = '21150000') AS '亳州市', " +
				"(SELECT COUNT(*) FROM tb_b_resource WHERE del_flag = '0' AND city_id = '21160000') AS '池州市', " +
				"(SELECT COUNT(*) FROM tb_b_resource WHERE del_flag = '0' AND city_id = '21170000') AS '宣城市' ";
		return this.jdbcTemplate.queryForList(sql);
	}
}